package com.lightsh.db;

import java.util.ArrayList;

import com.lightsh.common.NoteItem;
import com.lightsh.utility.ByteUtil;
import com.lightsh.utility.Constants;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * 一个操作数据库的帮助类
 * @author lightsh
 *
 */
public class MyDbHelper{
	
	private static final boolean DEBUG = Constants.DEBUG;
	private static final String TAG = "HandwrittenDbHelper";
	private static final int DATABASE_VERSION = 1;
	
	private static final String DATABASE_NAME = "handwritten_db";
	public static final String TABLE_NAME_NOTE = "notes_table";
	public static final String TABLE_NAME_CHARACTER = "character_table";
	
	public static final String TABLE_NAME_PAINT = "paint_table";
	public static final String TABLE_NAME_STROKE = "stroke_table";
	
	// 数据表中的字段
	public static final String NOTE_FIELD_ID = "_id";							//< 自增id值
	public static final String NOTE_FIELD_KEY = "key";						//< 便签的key值
	public static final String NOTE_FIELD_TYPE = "type";						//< 类型
	public static final String NOTE_FIELD_TEXT = "text_value";			    //< 内容
	public static final String NOTE_FIELD_TIME = "time";						//< 一个long值，记录时间
	public static final String NOTE_FIELD_CREATE_TIME = "create_time";		//< 记录创建时间
	
	
	public static final String CHARACTER_FIELD_ID = "_id";					//< 自增id值
	public static final String CHARACTER_FIELD_KEY = "key";					// 对应便签的id
	public static final String CHARACTER_FIELD_INDEX = "character_index";		// 对应属于第几个字
	public static final String CHARACTER_FIELD_BITMAP = "bitmap";				// 字符bitmap的byte数组
	
	public static final String PAINT_FIELD_ID = "_id";
	public static final String PAINT_FIELD_TEXT = "text_value";
	public static final String PAINT_FIELD_TYPE = "type";
	public static final String PAINT_FIELD_TIME = "time";
	public static final String PAINT_FIELD_CREATE_TIME = "create_time";
	public static final String PAINT_FIELD_WIDTH = "width";
	public static final String PAINT_FIELD_HEIGHT = "height";
	
	public static final String STROKE_FIELD_ID = "_id";
	public static final String STROKE_FIELD_KEY = "key";
	public static final String STROKE_FIELD_INDED = "stroke_index";
	public static final String STROKE_FIELD_COLOR = "color";
	public static final String STROKE_FIELD_TYPE = "type";
	public static final String STROKE_FIELD_STROKE_WIDTH = "width";
	public static final String STROKE_FIELD_POINTS = "points";
	
	public static final String[] NOTE_COLUMNS_ALL = {
		NOTE_FIELD_ID,
		NOTE_FIELD_KEY,
		NOTE_FIELD_TYPE,
		NOTE_FIELD_TEXT,
		NOTE_FIELD_TIME,
		NOTE_FIELD_CREATE_TIME
	};
	public static final String[] CHARACTER_COLUMNS_ALL = {
		CHARACTER_FIELD_ID,
		CHARACTER_FIELD_KEY,
		CHARACTER_FIELD_INDEX,
		CHARACTER_FIELD_BITMAP
	};
	public static final String[] PAINT_COLUMNS_ALL = {
		PAINT_FIELD_ID,
		PAINT_FIELD_TEXT,
		PAINT_FIELD_TYPE,
		PAINT_FIELD_TIME,
		PAINT_FIELD_CREATE_TIME,
		PAINT_FIELD_WIDTH,
		PAINT_FIELD_HEIGHT
	};
	public static final String[] STROKE_COLUMNS_ALL = {
		STROKE_FIELD_ID,
		STROKE_FIELD_KEY,
		STROKE_FIELD_INDED,
		STROKE_FIELD_COLOR,
		STROKE_FIELD_TYPE,
		STROKE_FIELD_STROKE_WIDTH,
		STROKE_FIELD_POINTS
	};
	
	// 创建数据表db的sql语句(插入时id值传入null则自增)
	private static final String SQL_CREATE_TABLE_NOTE = "create table " + TABLE_NAME_NOTE + " ( " 
												+ NOTE_FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
												+ NOTE_FIELD_KEY + " VARCHAR(64),"
												+ NOTE_FIELD_TYPE + " INTEGER,"
												+ NOTE_FIELD_TEXT + " text,"
												+ NOTE_FIELD_CREATE_TIME + " LONG,"
												+ NOTE_FIELD_TIME + " LONG )";
	
	private static final String SQL_CREATE_TABLE_CHARACTER = "create table " + TABLE_NAME_CHARACTER + " ( " 
			+ CHARACTER_FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ CHARACTER_FIELD_KEY + " INTEGER,"
			+ CHARACTER_FIELD_INDEX + " INTEGER,"
			+ CHARACTER_FIELD_BITMAP + " BLOB)";
	

	private static final String SQL_CREATE_TABLE_PAINT = "create table " + TABLE_NAME_PAINT + " ( " 
			+ PAINT_FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ PAINT_FIELD_TYPE + " INTEGER,"
			+ PAINT_FIELD_TEXT + " text,"
			+ PAINT_FIELD_CREATE_TIME + " LONG,"
			+ PAINT_FIELD_TIME + " LONG,"
			+ PAINT_FIELD_WIDTH + " INTEGER,"
			+ PAINT_FIELD_HEIGHT + " INTEGER )";
	
	private static final String SQL_CREATE_TABLE_STROKE = "create table " + TABLE_NAME_STROKE + " ( " 
			+ STROKE_FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ STROKE_FIELD_KEY + " INTEGER,"
			+ STROKE_FIELD_INDED + " INTEGER,"
			+ STROKE_FIELD_COLOR + " INTEGER,"
			+ STROKE_FIELD_TYPE + " INTEGER,"
			+ STROKE_FIELD_STROKE_WIDTH + " INTEGER,"
			+ STROKE_FIELD_POINTS + " BLOB)";
	
	// 刷新数据表的sql语句
	private static final String SQL_UPGRADE_TABLE_NOTE = "DROP TABLE IF EXISTS " + TABLE_NAME_NOTE;
	private static final String SQL_UPGRADE_TABLE_CHARACTER = "DROP TABLE IF EXISTS " + TABLE_NAME_CHARACTER;
	private static final String SQL_UPGRADE_TABLE_PAINT = "DROP TABLE IF EXISTS " + TABLE_NAME_PAINT;
	private static final String SQL_UPGRADE_TABLE_STROKE = "DROP TABLE IF EXISTS " + TABLE_NAME_STROKE;
	
	
	private Context mContext = null;
	private MyDatabaseHelper mDatabaseHelper = null;
	
	private static void log(String msg){
		if(DEBUG && msg != null){
			Log.d(TAG, msg);
		}
	}
	
	private static class MyDatabaseHelper extends SQLiteOpenHelper{

		/**
		 * 如果传入的版本号跟旧的版本号不一致，则会执行{{@link #onUpgrade(SQLiteDatabase, int, int)}方法
		 * @param context
		 */
		public MyDatabaseHelper(Context context){
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			log("onCreate");
			db.execSQL(SQL_CREATE_TABLE_NOTE);
			db.execSQL(SQL_CREATE_TABLE_CHARACTER);
			db.execSQL(SQL_CREATE_TABLE_PAINT);
			db.execSQL(SQL_CREATE_TABLE_STROKE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			log("onUpgrade");
			// 当数据库版本变更后，我们选择丢弃所有数据，并重新创建
			// 可以考虑根据版本号变更进行数据库修复操作		
			
			if(oldVersion == 1 && newVersion == 2){
				// 记录之前的老数据
				ArrayList<NoteItem> list = new ArrayList<NoteItem>();
				String[] columnString = new String[]{
						NOTE_FIELD_ID,
						NOTE_FIELD_KEY,
						NOTE_FIELD_TYPE,
						NOTE_FIELD_TEXT,
						NOTE_FIELD_TIME
				};
				Cursor cursor = db.query(TABLE_NAME_NOTE, columnString, null, null, null, null, null);
				try{
					if(cursor != null && cursor.moveToFirst()){
						if(cursor.moveToFirst()){
							// 需要根据旧版本的数据进行处理
							int idColumn = cursor.getColumnIndex(MyDbHelper.NOTE_FIELD_ID);
							int keyColumn = cursor.getColumnIndex(MyDbHelper.NOTE_FIELD_KEY);
							int timeColumn = cursor.getColumnIndex(MyDbHelper.NOTE_FIELD_TIME);	  
							int textColumn = cursor.getColumnIndex(MyDbHelper.NOTE_FIELD_TEXT);	
							// no NOTE_FIELD_CREATE_TIME in old version
//							int createTimeColumn = cursor.getColumnIndex(MyDbHelper.NOTE_FIELD_CREATE_TIME);			
		        			while(true){
		        				list.add(new NoteItem(cursor.getInt(idColumn), cursor.getString(keyColumn), 
		        						cursor.getLong(timeColumn), cursor.getString(textColumn), cursor.getLong(timeColumn)));
		        				if(!cursor.moveToNext()){
		        					break;
		        				}
		        			}		        			
		            	}
					}		
				}finally{
					if(cursor != null){
						cursor.close();
					}
				}
				// create new table and insert,need not drop chrarcter table
				Log.w(TAG, "drop table note and create new...");
				db.execSQL(SQL_UPGRADE_TABLE_NOTE);
				db.execSQL(SQL_CREATE_TABLE_NOTE);
				if(!list.isEmpty()){
					Log.w(TAG, "restore data size = " + list.size());
					
					for(NoteItem item : list){
						ContentValues initValues = new ContentValues();
						initValues.put(NOTE_FIELD_ID, item.getId());
						initValues.put(NOTE_FIELD_KEY, item.getKey());
						initValues.put(NOTE_FIELD_TYPE, item.getType());
						initValues.put(NOTE_FIELD_TEXT, item.getText());
						initValues.put(NOTE_FIELD_TIME, item.getLastlyTime());
						initValues.put(NOTE_FIELD_CREATE_TIME, item.getCreateTime());
						if(DEBUG){
							log( "restore : " + item.getId() + "," + item.getText() + ". " + (db.insert(TABLE_NAME_NOTE, null, initValues) != -1 ? "success" : "fail"));
						}
						// FIXME 这里需要根据insert的id值，更新charater表的对应节点
					}
				}
				
			}else if(oldVersion > newVersion){
				Log.w(TAG, "old version is bigger, do nothing!");
			}else{
				Log.w(TAG, "drop all table and create new...");
				db.execSQL(SQL_UPGRADE_TABLE_NOTE);
				db.execSQL(SQL_UPGRADE_TABLE_CHARACTER);
				db.execSQL(SQL_UPGRADE_TABLE_PAINT);
				db.execSQL(SQL_UPGRADE_TABLE_STROKE);
				onCreate(db);		
			}
		}
		
	}

	
	public MyDbHelper(Context context){
		mContext = context;
		
		// 创建该对象不会执行创建数据库的操作，不会耗费时间
		mDatabaseHelper = new MyDatabaseHelper(mContext);
	}
	
	private SQLiteDatabase getWritableDatabase(){
		return mDatabaseHelper.getWritableDatabase();
	}
	private SQLiteDatabase getReadableDatabase(){
		return mDatabaseHelper.getReadableDatabase();
	}
	
	/**
	 *  使用完，必须使用该方法关闭该数据库。多次调用该方法不会造成问题。</p>
	 *  一般的合理做法是: 始终在不使用该helper时调用该方法。
	 */
	public void close(){
		synchronized (this) {
			if(mDatabaseHelper != null){
				mDatabaseHelper.close();
				mDatabaseHelper = null;
			}
		}
	}
	
	/**
	 *  获取所有数据
	 */
	public Cursor fetchAllNoteDataOrderbyTime(){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME_NOTE, NOTE_COLUMNS_ALL, null, null, null, null, NOTE_FIELD_TIME + " DESC");
	}
	public Cursor fetchAllCharacterData(){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME_CHARACTER, CHARACTER_COLUMNS_ALL, null, null, null, null, null);
	}
	public Cursor fetchAllPaintOrderbyTime(){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME_PAINT, PAINT_COLUMNS_ALL, null, null, null, null, PAINT_FIELD_TIME + " DESC");
	}
	
	/**
	 * 插入数据
	 * 插入时id值传入null则自增
	 * @return 插入的row ID值, 如果出错返回-1
	 */
	public long insertNoteData(String key, int type, String text, long time, long createTime){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(NOTE_FIELD_KEY, key);
		initValues.put(NOTE_FIELD_TYPE, type);
		initValues.put(NOTE_FIELD_TEXT, text);
		initValues.put(NOTE_FIELD_TIME, time);
		initValues.put(NOTE_FIELD_CREATE_TIME, createTime);
		return db.insert(TABLE_NAME_NOTE, null, initValues);
	}
	public boolean updateNoteData(long id, String text, long time){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(NOTE_FIELD_TIME, time);
		initValues.put(NOTE_FIELD_TEXT, text);		
		return db.update(TABLE_NAME_NOTE, initValues, NOTE_FIELD_ID + "=" + id, null) > 0;		
	}
	/**
	 * @return 插入的row ID值, 如果出错返回-1
	 * @param text
	 * @param type
	 * @param time
	 * @param createTime
	 * @param width
	 * @param height
	 * @return
	 */
	public long insertPaintData(String text, int type, long time, long createTime, int width, int height){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(PAINT_FIELD_TEXT, text);
		initValues.put(PAINT_FIELD_TYPE, type);
		initValues.put(PAINT_FIELD_TIME, time);
		initValues.put(PAINT_FIELD_CREATE_TIME, createTime);
		initValues.put(PAINT_FIELD_WIDTH, width);
		initValues.put(PAINT_FIELD_HEIGHT, height);		
		return db.insert(TABLE_NAME_PAINT, null, initValues);
	}
	public boolean updatePaintData(long id, String text, long time){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(PAINT_FIELD_TEXT, text);
		initValues.put(PAINT_FIELD_TIME, time);
		return db.update(TABLE_NAME_PAINT, initValues, PAINT_FIELD_ID + "=" + id, null) > 0;
	}
	
	/**
	 * 插入数据
	 * @key 	对应note数据的_id值
	 */
	public long insertCharacterData(long key, int index, byte[] bitmap){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(CHARACTER_FIELD_KEY, key);
		initValues.put(CHARACTER_FIELD_BITMAP, bitmap);
		initValues.put(CHARACTER_FIELD_INDEX, index);
		return db.insert(TABLE_NAME_CHARACTER, null, initValues);
	}
	public Cursor getNoteCharacter(long id){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME_CHARACTER, CHARACTER_COLUMNS_ALL, CHARACTER_FIELD_KEY + "=" + id, null, null, null, CHARACTER_FIELD_INDEX);
	}
	
	public long insertStrokesData(long key, int index, int color, int type, int width, float[] points){
		SQLiteDatabase db = getWritableDatabase();
		ContentValues initValues = new ContentValues();
		initValues.put(STROKE_FIELD_KEY, key);
		initValues.put(STROKE_FIELD_INDED, index);
		initValues.put(STROKE_FIELD_COLOR, color);
		initValues.put(STROKE_FIELD_TYPE, type);
		initValues.put(STROKE_FIELD_STROKE_WIDTH, width);
		initValues.put(STROKE_FIELD_POINTS, ByteUtil.toByteArray(points));
		return db.insert(TABLE_NAME_STROKE, null, initValues);
	}
	public Cursor getPaintStroke(long id){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME_STROKE, STROKE_COLUMNS_ALL, STROKE_FIELD_KEY + "=" + id, null, null, null, STROKE_FIELD_INDED);
	}
	
	public int updateErrorStrokeWidth(int initWidth){
		SQLiteDatabase db = getWritableDatabase();		
		ContentValues initValues = new ContentValues();
		initValues.put(STROKE_FIELD_STROKE_WIDTH, initWidth);		
		return db.update(TABLE_NAME_STROKE, initValues, STROKE_FIELD_STROKE_WIDTH + " < 0", null);
	}
	
	/**
	 * 删除指定便签对应的文字
	 * @param id
	 * @return			删除的个数
	 */
	public int deleteCharactersByNote(long id){
		SQLiteDatabase db = getWritableDatabase();
		final int deleteCount = db.delete(TABLE_NAME_CHARACTER, CHARACTER_FIELD_KEY + "=" + id, null);
		log("delete " + deleteCount + " characters for note " + id);
		return deleteCount;
	}
	public int deleteStrokeByPaint(long id){
		SQLiteDatabase db = getWritableDatabase();
		final int deleteCount = db.delete(TABLE_NAME_STROKE, STROKE_FIELD_KEY + "=" + id, null);
		log("delete " + deleteCount + " strokes for paint " + id);
		return deleteCount;
	}
	/**
	 *  删除指定的便签和对应的bitmap
	 * @param id	指定的id值
	 * @return		是否成功删除
	 */
	public boolean deleteNoteDataAndCharacters(long id){
		SQLiteDatabase db = getWritableDatabase();
		boolean bSuccess = db.delete(TABLE_NAME_NOTE, NOTE_FIELD_ID + "=" + id, null) > 0;
		
		// 删除文字
		deleteCharactersByNote(id);
		return bSuccess;
	}
	public boolean deletePaintDataAndStrokes(long id){
		SQLiteDatabase db = getWritableDatabase();
		boolean bSuccess = db.delete(TABLE_NAME_PAINT, PAINT_FIELD_ID + "=" + id, null) > 0;
		deleteStrokeByPaint(id);
		return bSuccess;
	}
	
	/**
	 * 获取指定id的数据
	 */
	public Cursor fetchNoteData(long id){
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME_NOTE, 
				NOTE_COLUMNS_ALL, 
				NOTE_FIELD_ID + "=" + id , null, null, null, null);
		return cursor;
	}
	public Cursor fetchPaintData(long id){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(TABLE_NAME_PAINT, PAINT_COLUMNS_ALL, PAINT_FIELD_ID + "=" + id, null, null, null, null);
	}
	
	/**
	 * 查询指定id值是否存在
	 */
	public boolean checkIfNoteExist(long id){
		Cursor cursor = fetchNoteData(id);
		if(cursor != null ){
			if(cursor.getCount() > 0){
				cursor.close();
				return true;
			}else{
				cursor.close();
				return false;
			}
		}else{
			return false;
		}
	}	
	
	
	/**
	 * 查询数据
	 * @param table The table name to compile the query against.
     * @param columns A list of which columns to return. Passing null will
     *            return all columns, which is discouraged to prevent reading
     *            data from storage that isn't going to be used.
     * @param selection A filter declaring which rows to return, formatted as an
     *            SQL WHERE clause (excluding the WHERE itself). Passing null
     *            will return all rows for the given table.
     * @param selectionArgs You may include ?s in selection, which will be
     *         replaced by the values from selectionArgs, in order that they
     *         appear in the selection. The values will be bound as Strings.
     * @param groupBy A filter declaring how to group rows, formatted as an SQL
     *            GROUP BY clause (excluding the GROUP BY itself). Passing null
     *            will cause the rows to not be grouped.
     * @param having A filter declare which row groups to include in the cursor,
     *            if row grouping is being used, formatted as an SQL HAVING
     *            clause (excluding the HAVING itself). Passing null will cause
     *            all row groups to be included, and is required when row
     *            grouping is not being used.
     * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
     *            (excluding the ORDER BY itself). Passing null will use the
     *            default sort order, which may be unordered.
     * @return A {@link Cursor} object, which is positioned before the first entry. Note that
     * {@link Cursor}s are not synchronized, see the documentation for more details.
	 */
	public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){
		SQLiteDatabase db = getReadableDatabase();
		return db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
	}
	
	/**
	 * 更新数据
	 * @param table the table to update in
     * @param values a map from column names to new column values. null is a
     *            valid value that will be translated to NULL.
     * @param whereClause the optional WHERE clause to apply when updating.
     *            Passing null will update all rows.
     * @return the number of rows affected
	 */
	public int update(String table, ContentValues values, String whereClause, String[] whereArgs){
		SQLiteDatabase db = getWritableDatabase();
		return db.update(table, values, whereClause, whereArgs);
	}
	
	/**
	 * 删除数据
	 * @param table the table to delete from
     * @param whereClause the optional WHERE clause to apply when deleting.
     *            Passing null will delete all rows.
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
	 */
	public int delete(String table, String whereClause, String[] whereArgs){
		SQLiteDatabase db = getWritableDatabase();
		return db.delete(table, whereClause, whereArgs);
	}
	
	/**
	 * 插入数据
	 * @param table the table to insert the row into
     * @param nullColumnHack SQL doesn't allow inserting a completely empty row,
     *            so if initialValues is empty this column will explicitly be
     *            assigned a NULL value
     * @param values this map contains the initial column values for the
     *            row. The keys should be the column names and the values the
     *            column values
     * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(String table, String nullColumnHack, ContentValues values){
		SQLiteDatabase db = getWritableDatabase();
		return db.insert(table, nullColumnHack, values);
	}
	
	
	/**
	 *  执行一些sql语句(非查询的sql语句)
	 */
	public void execSql(String sql) throws SQLException{
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL(sql);
	}
	
	/**
	 *  执行自定义的查询语句
	 */
	public Cursor execSqlQuery(String sql){
		SQLiteDatabase db = getWritableDatabase();
		return db.rawQuery(sql, null);
	}
	
	/**
	 * 将结果打印
	 * @return
	 */
	public static final String toString(Cursor cursor){
		if(cursor != null && cursor.moveToFirst()){
			StringBuilder builder = new StringBuilder();
			final int columnsCount = cursor.getColumnCount();
			while(true){
				for(int i = 0; i < columnsCount; ++i){
					builder.append(cursor.getColumnName(i)).append(":").append(cursor.getString(i));
					if(i != columnsCount -1){
						builder.append(",");
					}
				}
				if(!cursor.moveToNext()){
					break;
				}
				builder.append("\n");
			}
			return builder.toString();
		}
		return "";
	}
}

